package customerreports;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.sql.*;
import java.io.IOException;
import java.io.FileOutputStream;
import java.util.*;

/**
 * Created by IntelliJ IDEA.
 * User: raj.chhabra
 * Date: Nov 16, 2010
 * Time: 11:24:09 AM
 * To change this template use File | Settings | File Templates.
 */
public class CustomerReport {

        Map prevYearResultsMap = new HashMap();
        Map customerMap = new HashMap();
        List<String> customersAlreadyAdded = new ArrayList<String>();
    
private static final String[] titles = {
            "Customer Code", "Store Code","Last 2010 Order Date", "Last 2010 Order Total", "2010 Total",
            "First Name", "Last Name","Corp Name","Address 1","Address 2","City","State","Zip","Home Phone","Business Phone","Extension","Email Address"};


    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
        Map<String,String> prevYearResultsMap = new HashMap<String,String>();
        Map<String,Customer> customerMap = new HashMap<String,Customer>();
        List<String> customersAlreadyAdded = new ArrayList<String>();


        Class.forName("oracle.jdbc.driver.OracleDriver");
        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

        //Connection conn=db.dbConnect("jdbc:oracle:thin:@gers.grangeny.com:1521/glive", "sqllive", "live01");

        //Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@gers.grangeny.com:1521/glive", "sqllive", "live01");

        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@gers.grangeny.com:1521/clive", "sqllive", "live01");

        /*String lastOrderQuery = "select t2.cust_cd,orderdate,sum(total_per_order) as totalperorder from " +
                "(select t1.orderdate,t1.del_doc_num,t1.cust_cd, " +
                "(select sum(unit_prc*qty) from so_ln where so_ln.del_doc_num=t1.del_doc_num group by del_doc_num) as TOTAL_PER_ORDER " +
                "from ( " +
                "select max(SO.SO_WR_DT) as orderdate, del_doc_num,cust_cd from so group by del_doc_num,cust_cd order by orderdate desc " +
                ") t1 " +
                ")t2 group by orderdate,cust_cd";*/

        /*String lastOrderQuery = "select t2.cust_cd,orderdate,so_store_cd,sum(total_per_order) as totalperorder from " +
                "(select t1.orderdate,t1.del_doc_num,t1.cust_cd,t1.so_store_cd, " +
                "(select sum(unit_prc*qty) from so_ln where so_ln.del_doc_num=t1.del_doc_num group by del_doc_num) as TOTAL_PER_ORDER\n" +
                "from ( " +
                "select max(SO.SO_WR_DT) as orderdate, del_doc_num,cust_cd,so_store_cd from so where SO.STAT_CD in ('F','O') " +
                " AND SO.ORD_TP_CD='SAL' group by del_doc_num,cust_cd,so_store_cd order by orderdate desc " +
                ") t1 " +
                ")t2 group by orderdate,cust_cd,so_store_cd order by so_store_cd, orderdate";*/

        /*String lastOrderQuery = "select t2.cust_cd,orderdate,so_store_cd,sum(total_per_order) as totalperorder from \n" +
                "(select " +
                "t1.orderdate,t1.del_doc_num,t1.cust_cd,t1.so_store_cd, " +
                "(select sum(unit_prc*qty) from so_ln where so_ln.del_doc_num=t1.del_doc_num group by del_doc_num) as TOTAL_PER_ORDER " +
                "from ( " +
                "select max(SO.SO_WR_DT) as orderdate, del_doc_num,cust_cd,so_store_cd from so where SO.STAT_CD in ('F','O')" +
                " AND SO.ORD_TP_CD='SAL' group by del_doc_num,cust_cd,so_store_cd order by orderdate desc " +
                ") t1 " +
                ") t2 group by orderdate,cust_cd,so_store_cd order by so_store_cd,orderdate desc,cust_cd";*/

        String lastOrderQuery = "select t3.* from (" +
                "select t2.cust_cd,orderdate,so_store_cd,sum(total_per_order) as totalperorder from " +
                "(select " +
                "t1.orderdate,t1.del_doc_num,t1.cust_cd,t1.so_store_cd, " +
                "(select sum(unit_prc*qty) from so_ln where so_ln.del_doc_num=t1.del_doc_num group by del_doc_num) as TOTAL_PER_ORDER " +
                "from ( " +
                "select max(SO.SO_WR_DT) as orderdate, del_doc_num,cust_cd,so_store_cd from so where SO.STAT_CD in ('F','O') " +
                " AND SO.ORD_TP_CD='SAL' group by del_doc_num,cust_cd,so_store_cd order by orderdate desc " +
                ") t1 " +
                ") t2 group by orderdate,cust_cd,so_store_cd order by so_store_cd,orderdate desc,cust_cd " +
                ") t3 where totalperorder>0";


        String lastOrderQueryWithDates = "select t3.* from (" +
                "select t2.cust_cd,orderdate,so_store_cd,sum(total_per_order) as totalperorder from " +
                "(select " +
                "t1.orderdate,t1.del_doc_num,t1.cust_cd,t1.so_store_cd, " +
                "(select sum(unit_prc*qty) from so_ln where so_ln.del_doc_num=t1.del_doc_num and SO_LN.VOID_FLAG='N'group by del_doc_num) as TOTAL_PER_ORDER " +
                "from ( " +
                "select max(SO.SO_WR_DT) as orderdate, del_doc_num,cust_cd,so_store_cd from so where SO.STAT_CD in ('F','O') " +
                " AND SO.ORD_TP_CD='SAL' and so_wr_dt between ('01-Jan-2010') and ('31-DEC-2010')" +
                " group by del_doc_num,cust_cd,so_store_cd order by orderdate desc " +
                ") t1 " +
                ") t2 group by orderdate,cust_cd,so_store_cd order by so_store_cd,orderdate desc,cust_cd " +
                ") t3 where totalperorder>0";


        String prevYearTotalQuery = "select t1.cust_cd,max(t1.unit_prc*t1.qty) as prevYearTotal" +
                " from (select cust_cd,so.del_doc_num,so.so_wr_dt,unit_prc,qty from so,so_ln where " +
                " (so_wr_dt >= sysdate-365 AND so_wr_dt<=sysdate-1) " +
                " AND SO.STAT_CD in ('F','O') " +
                " AND SO.ORD_TP_CD='SAL' " +
                //" AND SO.SO_STORE_CD='01' " +
                " AND SO_LN.VOID_FLAG='N' " +
                " AND so.del_doc_num=so_ln.del_doc_num " +
                " ) t1 group by t1.cust_cd order by cust_cd asc";

        String prevYearTotalQueryWithDates = "select t1.cust_cd,sum(t1.unit_prc*t1.qty) as prevYearTotal" +
                " from (select cust_cd,so.del_doc_num,so.so_wr_dt,unit_prc,qty from so,so_ln where " +
                " (so_wr_dt >= ('01-Jan-2010') AND so_wr_dt<=('31-DEC-2010')) " +
                " AND SO.STAT_CD in ('F','O') " +
                " AND SO.ORD_TP_CD='SAL' " +
                //" AND SO.SO_STORE_CD='01' " +
                " AND SO_LN.VOID_FLAG='N' " +
                " AND so.del_doc_num=so_ln.del_doc_num " +
                " ) t1 group by t1.cust_cd order by cust_cd asc";

        String custQuery = "select cust_cd,Fname,LName,Corp_Name,ADDR1,ADDR2,City,St_cd,Zip_cd,home_phone,Bus_phone,Ext,Email_Addr " +
                "from Cust";

        Statement stmt = conn.createStatement();
        Statement stmt2 = conn.createStatement();
        Statement stmt3 = conn.createStatement();

        ResultSet lastOrderResults = stmt.executeQuery(lastOrderQueryWithDates);
        ResultSet prevYearResults = stmt2.executeQuery(prevYearTotalQueryWithDates);
        ResultSet customers = stmt3.executeQuery(custQuery);

        while(prevYearResults.next()){
            StringBuffer sb = new StringBuffer();
            String custCode = prevYearResults.getString("cust_cd");
            String prevYearTotal = prevYearResults.getString("prevYearTotal");
            //sb.append(custCode + "\t ");
            //sb.append(prevYearTotal + "\t ");
            //System.out.println(sb.toString());
            prevYearResultsMap.put(custCode.trim(),prevYearTotal);
        }

        int custCount = 0;
        while(customers.next()){
            custCount++;
            String custCode = customers.getString("cust_cd");
            String firstName = customers.getString("Fname");
            String lastName = customers.getString("Lname");
            String corpName = customers.getString("corp_name");
            String addr1 = customers.getString("addr1");
            String addr2 = customers.getString("addr2");
            String city = customers.getString("city");
            String stateCode = customers.getString("st_cd");
            String zipCode = customers.getString("zip_cd");
            String homePhone = customers.getString("home_phone");
            String businessPhone = customers.getString("bus_phone");
            String extension = customers.getString("ext");
            String emailAddress = customers.getString("email_addr");

            Customer customer = new Customer();
            customer.setCustCode(custCode);
            customer.setFirstName(firstName);
            customer.setLastName(lastName);
            customer.setCorpName(corpName);
            customer.setAddr1(addr1);
            customer.setAddr2(addr2);
            customer.setCity(city);
            customer.setStateCode(stateCode);
            customer.setZipCode(zipCode);
            customer.setHomePhone(homePhone);
            customer.setBusinessPhone(businessPhone);
            customer.setExt(extension);
            customer.setEmailAddress(emailAddress);

            customerMap.put(custCode.trim(),customer);
            if (custCount % 500 == 0)
            System.out.println("Added customer #: " + custCount);
        }


        //Create spreadsheet and add header row
        Workbook wb = new HSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = wb.createSheet("Customer Report");

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);

        //the following three statements are required only for HSSF
        sheet.setAutobreaks(true);
        printSetup.setFitHeight((short)1);
        printSetup.setFitWidth((short)1);

        Row titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(12.75f);
        Cell cell = titleRow.createCell(0);
        cell.setCellValue("Customer Report for Jan 1, 2010 to Dec. 31, 2010");
        cell.setCellStyle(styles.get("header"));


        //the header row: centered text in 48pt font
        Row headerRow = sheet.createRow(1);
        headerRow.setHeightInPoints(12.75f);
        for (int i = 0; i < titles.length; i++) {
            cell = headerRow.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(styles.get("header"));
        }

        Row row;
        int rownum = 2;
        String lastStoreCode = "";
        int customersSkippedCount = 0;
        while(lastOrderResults.next()){

            String orderDate;
            String storeCode;
            String totalPerOrder;
            
            int j=0;
            row = sheet.createRow(rownum);
            String prevYearTotal = null;
            Customer customer = null;
            StringBuffer sb = new StringBuffer();
            String custCode = lastOrderResults.getString("cust_cd");
            customer = (Customer)customerMap.get(custCode);
            prevYearTotal = (String)prevYearResultsMap.get(custCode);
            orderDate = lastOrderResults.getDate("orderdate").toString();
            storeCode = lastOrderResults.getString("so_store_cd");
            totalPerOrder = lastOrderResults.getString("totalperorder");

            if (!lastStoreCode.equalsIgnoreCase(storeCode)){
                customersAlreadyAdded = new ArrayList<String>();
                customersSkippedCount=0;
                lastStoreCode = storeCode;
            }

            if (customersAlreadyAdded.contains(custCode)){
                customersSkippedCount++;
                System.out.println("Customer Skipped Count = "+customersSkippedCount);
                continue;
            }
            else {
                customersAlreadyAdded.add(custCode);
            }

            

            sb.append(orderDate + "\t ");
            sb.append(storeCode + "\t ");
            sb.append(totalPerOrder + "\t ");

            if (prevYearTotal != null)
                sb.append("PYT=" + prevYearTotal);
            else
                sb.append("PYT=0");

            cell = row.createCell(j);
            cell.setCellValue(custCode);
            cell.setCellStyle(styles.get("cell_normal"));
            j++;

            cell = row.createCell(j);
            cell.setCellValue(storeCode);
            cell.setCellStyle(styles.get("cell_normal"));
            j++;

            cell = row.createCell(j);
            cell.setCellValue(orderDate);
            cell.setCellStyle(styles.get("cell_normal"));
            j++;

            cell = row.createCell(j);
            cell.setCellValue(totalPerOrder);
            cell.setCellStyle(styles.get("cell_normal_money"));
            j++;

            cell = row.createCell(j);
            cell.setCellValue(prevYearTotal);
            cell.setCellStyle(styles.get("cell_normal_money"));
            j++;




            if (customer != null){

                cell = row.createCell(j);
                cell.setCellValue(customer.getFirstName());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getLastName());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getCorpName());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getAddr1());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getAddr2());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getCity());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getStateCode());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getZipCode());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getHomePhone());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getBusinessPhone());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getExt());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

                cell = row.createCell(j);
                cell.setCellValue(customer.getEmailAddress());
                cell.setCellStyle(styles.get("cell_normal"));
                j++;

            sb.append(customer.getFirstName());
            sb.append(customer.getLastName());
            sb.append(customer.getCorpName());
            sb.append(customer.getAddr1());
            sb.append(customer.getAddr2());
            sb.append(customer.getCity());
            sb.append(customer.getStateCode());
            sb.append(customer.getZipCode());
            sb.append(customer.getHomePhone());
            sb.append(customer.getBusinessPhone());
            sb.append(customer.getExt());
            sb.append(customer.getEmailAddress());
            }

            System.out.println(sb.toString());
            rownum++;
        }

        Sheet sheet1 = wb.getSheetAt(0);

        for (int k=0; k<17; k++){
            sheet1.autoSizeColumn((short)k);
        }

        // Write the output to a file
        String file = args[0];  //"CustomerReportCAN.xls";
        if(wb instanceof XSSFWorkbook) file += "x";
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();
        
        stmt.close();
        stmt2.close();
        stmt3.close();
    }

    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        DataFormat df = wb.createDataFormat();

        CellStyle style;
        Font headerFont = wb.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        styles.put("header", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("header_date", style);

        Font font1 = wb.createFont();
        font1.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(font1);
        styles.put("cell_b", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font1);
        styles.put("cell_b_centered", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setFont(font1);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_b_date", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setFont(font1);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_g", style);

        Font font2 = wb.createFont();
        font2.setColor(IndexedColors.BLUE.getIndex());
        font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(font2);
        styles.put("cell_bb", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setFont(font1);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_bg", style);

        Font font3 = wb.createFont();
        font3.setFontHeightInPoints((short)14);
        font3.setColor(IndexedColors.DARK_BLUE.getIndex());
        font3.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(font3);
        style.setWrapText(true);
        styles.put("cell_h", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setWrapText(true);
        styles.put("cell_normal", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setWrapText(true);
        styles.put("cell_normal_centered", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setWrapText(true);
        style.setDataFormat(df.getFormat("d-mmm"));
        styles.put("cell_normal_date", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setWrapText(true);
        style.setDataFormat(wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
        styles.put("cell_normal_money", style);

        style = createBorderedStyle(wb);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setIndention((short)1);
        style.setWrapText(true);
        styles.put("cell_indented", style);

        style = createBorderedStyle(wb);
        style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("cell_blue", style);

        return styles;
    }

    private static CellStyle createBorderedStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    
}
